package com.hns.tool.export.excel.excelTemplate;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URL;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import javax.imageio.ImageIO;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
 * Excel模板实现类(使用子列表，合并数据行。)
 * @author:Fisher
 * @email:zhuangcaijin@126.com
 * @version Revision 2.0.0
 */
public class ExcelRowspanTemplate {

	private static final Logger log = LoggerFactory.getLogger(ExcelRowspanTemplate.class);
	
	/**
	 * 模板文件名
	 */
	private String templateFile;

	/**
	 * 输出文件名
	 */
	private String outputFile;

	/**
	 * Excel模板定义的输出字段名数组
	 */
	private String[] fieldNames;

	/**
	 * 输出的起始行,默认为-1,不输出
	 */
	private int startRow = -1;

	/**
	 * 默认字体大小
	 */
	private int fontSize = 10;
	/**
	 * 默认字体
	 */
	private String fontName = "宋体";
	
	/**
	 * 默认属性key名
	 */
	private String SUBLIST = "list";

	/**
	 * 是否设置信息标题栏边框,默认情况不设置边框
	 */
	private boolean titleCellBold = true;

	/**
	 * 是否设置空白栏边框，默认情况不设置边框
	 */
	private boolean blankCellBold = false;
	
	/**
	 * 是否有公式
	 */
	private boolean hasFormula = false;

	/**
	 * 最大行高度
	 */
	private int maxRowHeight = 100;
	
	/**
	 * 单元格边框样式
	 */
	private HSSFCellStyle borderStyle = null;
	
	/**
	 * 无边框样式
	 */
	private HSSFCellStyle noneStyle = null;
	/**
	 * 关键字 &-表示模版信息内容字段 #-表示模版明细内容字段 formula-表示模版函数关键字
	 * ~-表示Cell当前行，当包含":"时，表示当前行减1
	 */
	private final static String TITLE_FLAG = "&";
	private final static String CONTENT_FLAG = "#";
	private final static String FORMULA_FLAG = "formula";
	private final static String UNLIMIT_FLAG = "~";
	private final static String FIELD_AUTO_ID = "_id";
	private final static String IMAGE_FLAG = "embedImage";

	/**
	 * 公式计算操作符号
	 */
	private final static String[] OP_FLAG = new String[] { "+", "-", "*", "/", "%",
			":" };

	/**
	 * 构造器(默认单行最高100)
	 * @param templateFile 模版文件
	 * @param outputFile 输出文件
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	public ExcelRowspanTemplate(String templateFile, String outputFile) {
		this.templateFile = templateFile;
		this.outputFile = outputFile;
	}
	
	/**
	 * 构造器
	 * @param templateFile 模版文件
	 * @param outputFile 输出文件
	 * @param subListName 属性key名
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	//TODO subListName
	public ExcelRowspanTemplate(String templateFile, String outputFile, String subListName) {
		this.templateFile = templateFile;
		this.outputFile = outputFile;
		this.SUBLIST = subListName;
	}

	/**
	 * 构造器
	 * @param templateFile 模版文件
	 * @param outputFile 输出文件
	 * @param subListName 属性key名
	 * @param maxRowHeight 行最大高度
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	//TODO subListName maxRowHeight
	public ExcelRowspanTemplate(String templateFile, String outputFile, String subListName, int maxRowHeight) {
		this.templateFile = templateFile;
		this.outputFile = outputFile;
		this.SUBLIST = subListName;
		this.maxRowHeight = maxRowHeight;
	}
	
	/**
	 * 设置模版文件是否包含Excel公式
	 * @param hasFormula 是否包含公式
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	public void setIncludeFormula(boolean hasFormula) {
		this.hasFormula = hasFormula;
	}

	/**
	 * 设置标题栏是否需要边框
	 * @param titleCellBold 是否加边框
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	public void setTitleCellBold(boolean titleCellBold) {
		this.titleCellBold = titleCellBold;
	}

	/**
	 * 设置空白行是否需要显示边框
	 * @param blankCellBold 是否加边框
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	public void setBlankCellBold(boolean blankCellBold) {
		this.blankCellBold = blankCellBold;
	}

	/**
	 * 设置字体大小，默认10号字体
	 * @param size 字体大小
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	public void setFontSize(int size) {
		this.fontSize = size;
	}

	/**
	 * 设置字体类型
	 * @param fontName 字体类型名称
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	public void setFontName(String fontName) {
		this.fontName = fontName;
	}

	/**
	 * 初始化工作模版，获取模版配置起始行(start)以及对应字段填充位置(fieldNames)
	 * @param sheet 工作表
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	private void initialize(HSSFSheet sheet) {
		boolean setStart = false;
		int rows = sheet.getPhysicalNumberOfRows();

		for (int r = 0; r < rows; r++) {
			HSSFRow row = sheet.getRow(r);

			if (row != null) {
				int cells = row.getPhysicalNumberOfCells();
				for (int c = 0; c < cells; c++) {
					HSSFCell cell = row.getCell(c);
					if (cell != null) {
						String value = null;
						if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
							value = "" + cell.getNumericCellValue();
						} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
							value = "" + cell.getBooleanCellValue();
						} else {
							value = cell.getRichStringCellValue().getString();
						}
						if (value != null && !"".equals(value)) {
							value = value.trim();
							// 内容数据
							if (value.startsWith(CONTENT_FLAG)) {
								if (!setStart) {
									this.startRow = r;// 设置内容填充起始行
									this.fieldNames = new String[cells];
									setStart = true;
								}
								this.fieldNames[c] = value.substring(1);// 初始化内容字段
							}

						}

					}

				}
			}
		}

	}

	/**
	 * 计算公式,默认范围从0行到工作薄结尾
	 * @param wb excel工作簿
	 * @param sheet 工作表
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	private void calcFormula(HSSFWorkbook wb, HSSFSheet sheet) {
		this.calcFormula(wb, sheet, 0, sheet.getPhysicalNumberOfRows());
	}

	/**
	 * 计算公式函数,范围从开始行(start_row)到结束行(end_row)
	 * @param wb excel工作簿
	 * @param sheet 工作表
	 * @param start_rang 开始行序号
	 * @param end_rang 结束行序号
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	private void calcFormula(HSSFWorkbook wb, HSSFSheet sheet, int start_rang,
			int end_rang) {
		// int rows = sheet.getPhysicalNumberOfRows();
		HSSFCellStyle borderStyle = this.getBorderStyle(wb);
		HSSFCellStyle noneStyle = this.getNoneStyle(wb);
		for (int r = start_rang; r < end_rang; r++) {
			HSSFRow row = sheet.getRow(r);
			if (row != null) {
				int cells = row.getPhysicalNumberOfCells();
				for (int c = 0; c < cells; c++) {
					HSSFCell cell = row.getCell(c);
					if (cell != null) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
							String value = cell.getRichStringCellValue()
									.getString();
							if (value != null) {
								value = value.trim().toLowerCase();
								if (value.startsWith(FORMULA_FLAG)) {
									int index = value.indexOf("=");
									String formula = value.substring(index + 1);
									// 判断函数是否包含以#开头,如果是以#开头表示必须显示边框，
									String flag = formula.substring(0, 1);
									boolean showBold = false;
									if (flag.equals(CONTENT_FLAG)) {
										formula = formula.substring(1);
										showBold = true;
									}
									// 如果包含':'符号则统计公式不包含当前行,否则会引发公式循环引用错误.
									if (formula.indexOf(":") != -1) {
										formula = formula.replaceAll(
												UNLIMIT_FLAG, r + "")
												.toUpperCase();
									} else {
										formula = formula.replaceAll(
												UNLIMIT_FLAG, (r + 1) + "")
												.toUpperCase();
									}

									cell = row.createCell(c);
									cell
											.setCellType(HSSFCell.CELL_TYPE_FORMULA);
									cell.setCellFormula(formula);
									// }

									if (showBold) {
										cell.setCellStyle(borderStyle);
									} else {
										cell.setCellStyle(noneStyle);
									}

								}
							}
						}
					}

				}
			}
		}
	}

	
	/**
	 * 生成填充模版标题数据
	 * @param exportInfo 包含图片路径的对象
	 * @param wb excel工作簿
	 * @param sheet 工作表
	 * @param patriarch 画图的顶级管理器
	 * @throws Exception
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	//TODO
	private void generateTitleDatas(Object exportInfo, HSSFWorkbook wb,
			HSSFSheet sheet, HSSFPatriarch patriarch) throws Exception {
		int rows = sheet.getPhysicalNumberOfRows();
		HSSFCellStyle borderStyle = this.getBorderStyle(wb);
		HSSFCellStyle noneStyle = this.getNoneStyle(wb);
		for (int r = 0; r < rows; r++) {
			HSSFRow row = sheet.getRow(r);
			if (row != null) {

				int cells = row.getPhysicalNumberOfCells();
				for (int c = 0; c < cells; c++) {
					HSSFCell cell = row.getCell(c);
					if (cell != null) {
						if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
							String value = cell.getRichStringCellValue()
									.getString();
							if (value != null) {
								value = value.trim();
								if (value.startsWith(TITLE_FLAG)) {
									value = value.substring(1);
									
									if (titleCellBold) {
										cell.setCellStyle(borderStyle);
									} else {
										cell.setCellStyle(noneStyle);
									}
									
									// 插入图片
									if (value.startsWith(IMAGE_FLAG)) {
										String imageFile = (String) getValue(exportInfo, value.substring(value.indexOf("=") + 1));	
										drawImage(wb, sheet, imageFile, cell, patriarch);
										cell.setCellValue("");
										continue;
									}
									
									// 获取对应的值，
									Object obj = getValue(exportInfo, value);
									
									if (obj instanceof Double
											|| obj instanceof BigDecimal
											|| obj instanceof Float) {
										cell = row.createCell(c);
//										HSSFCellStyle cellStyle = cell.getCellStyle();
//										HSSFDataFormat format = wb.createDataFormat();
//										cellStyle.setDataFormat(format
//												.getFormat("#,##0.00"));
										cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
										cell.setCellValue(Double.parseDouble(obj
												.toString()));
									} else if (obj instanceof Long
											|| obj instanceof Integer) {
										cell = row.createCell(c);
										cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
										cell.setCellValue(Long.valueOf(obj.toString()));
									} else if (obj instanceof Boolean) {
										cell = row.createCell(c);
										cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
										cell.setCellValue((Boolean)obj);
									} 
									else {
										cell.setCellType(HSSFCell.CELL_TYPE_STRING);
										cell.setCellValue(obj.toString());
										HSSFCellStyle cellStyle2;
										if (titleCellBold) {
											cellStyle2 = this.getBorderStyle(wb);
										} else {
											cellStyle2 = this.getNoneStyle(wb);
										}
										HSSFDataFormat format = wb.createDataFormat();   
										cellStyle2.setDataFormat(format.getFormat("@"));   
										cell.setCellStyle(cellStyle2); 
									}

									// 重建Cell，填充标题值
//									cell = row.createCell(c);
//									cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//									cell.setCellValue(new HSSFRichTextString(
//											content));

								}
							}
						}
					}

				}
			}
		}
	}
	
	/**
	 * 将指定的对象数组resulset输出到指定的Excel位置
	 * @param resultset List<?>对象数组
	 * @param wb excel工作簿
	 * @param sheet 工作表
	 * @param patriarch 画图的顶级管理器
	 * @throws Exception
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	private void generateContentDatas(List<?> resultset, HSSFWorkbook wb,
			HSSFSheet sheet, HSSFPatriarch patriarch) throws Exception {
		HSSFCellStyle borderStyle = this.getBorderStyle(wb);
		HSSFCellStyle noneStyle = this.getNoneStyle(wb);

		// 默认行号
		int autoRowId = 1;
		HSSFRow sourceRow = sheet.getRow(startRow);
		HSSFCellStyle[] css = new HSSFCellStyle[sourceRow.getPhysicalNumberOfCells()];
		for(int k=0; k < sourceRow.getPhysicalNumberOfCells(); k++) {
			css[k] = sourceRow.getCell(k).getCellStyle();
		}

		for (Iterator<?> it = resultset.iterator(); it.hasNext(); autoRowId++) {
			Object obj = it.next();
			
			
			int rowStart = startRow;
			HSSFRow superSourceRow = sheet.getRow(startRow);
			
			List<?> subList = (List<?>) getValue(obj, SUBLIST);
			
			int subCount = 0;
			for (Object content : subList) {
				sourceRow = sheet.getRow(startRow);
				HSSFRow row = sheet.createRow(startRow++);
				
				for (int i = 0; i < sourceRow.getPhysicalNumberOfCells(); i++) {
					log.trace("row[{}], fieldNames[{}] {}", startRow, i, fieldNames[i]);
					// 插入图片没试过
					if (fieldNames[i] != null
							&& fieldNames[i].startsWith(IMAGE_FLAG)) {
						String imageFile = (String) getValue(content, fieldNames[i]
								.substring(fieldNames[i].indexOf(".") + 1));
						HSSFCell cell = row.createCell(i);
						try {
							drawImage(wb, sheet, imageFile, cell, patriarch);
						} catch(Exception e){
							cell.setCellStyle(borderStyle);
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							cell.setCellValue(imageFile); 
						}
						continue;
					}
					if (fieldNames[i] != null && ! fieldNames[i].contains(".") ) {
						HSSFCell cell = row.createCell(i);
						cell.setCellStyle(css[i]);
					}
	
					if (fieldNames[i] != null && fieldNames[i].contains(".") ) {
						HSSFCell cell = row.createCell(i);
						cell.setCellStyle(css[i]);
						if (content != null) {
							// 字段名支持xpath取值
							Object value = getValue(content, fieldNames[i].substring(fieldNames[i].indexOf(".") + 1));
	
							if (value != null) {
								if (value instanceof Double
										|| value instanceof BigDecimal
										|| value instanceof Float) {
									cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
									cell.setCellValue(Double.parseDouble(value
											.toString()));
								} else if (value instanceof Long
										|| value instanceof Integer) {
									cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
									cell.setCellValue(Long.valueOf(value.toString()));
								} else if (value instanceof Boolean) {
									cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
									cell.setCellValue((Boolean)value);
								} 
								else {
									cell.setCellType(HSSFCell.CELL_TYPE_STRING);
									cell.setCellValue(value.toString());  
								}
							} else {
								cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
							}
						} else {
	
							cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
							if (!blankCellBold) {
								cell.setCellStyle(noneStyle);
							} else {
								cell.setCellStyle(borderStyle);
							}
						}
					} else {
						// 不搞公式啦没试过
						HSSFCell sourceCell = sourceRow.getCell(i);
						if (sourceCell != null
								&& sourceCell.getCellType() == HSSFCell.CELL_TYPE_STRING
								&& sourceCell.getRichStringCellValue().getString() != null
								&& sourceCell.getRichStringCellValue().getString()
										.toLowerCase().startsWith(FORMULA_FLAG)) {
	
							HSSFCell cell = row.createCell(i);
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							cell.setCellValue(sourceCell.getRichStringCellValue());
						}
					}
				}
				if (++subCount < subList.size()) {
					// 向下平推一行
					shiftDown(sheet, startRow - 1, sheet.getLastRowNum(), 1);
				}
			}

			if ( it.hasNext() ) {
				// 向下平推一行
				shiftDown(sheet, startRow - 1, sheet.getLastRowNum(), 1);
			}
			
			// 合并行，输出合并
			for (int i = 0; i < sourceRow.getPhysicalNumberOfCells(); i++) {
				// 非子列表的数据
				if (fieldNames[i] != null && ! fieldNames[i].contains(".") ) {
					log.trace("row[{}], start[{}] span to [{}]", startRow, rowStart, startRow);
					log.trace("row[{}], fieldNames[{}] {}", startRow, i, fieldNames[i]);
					sheet.addMergedRegion(new CellRangeAddress(rowStart, startRow-1, i, i));
					// TODO 输出值
					HSSFRow row = sheet.getRow(rowStart);
					HSSFCell cell = row.createCell(i);
					cell.setCellStyle(css[i]);
					if (obj != null) {
						// 字段名支持xpath取值
						Object value = getValue(obj, fieldNames[i]);

						if (value != null) {
							if (value instanceof Double
									|| value instanceof BigDecimal
									|| value instanceof Float) {
								cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
								cell.setCellValue(Double.parseDouble(value
										.toString()));
							} else if (value instanceof Long
									|| value instanceof Integer) {
								cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
								cell.setCellValue(Long.valueOf(value.toString()));
							} else if (value instanceof Boolean) {
								cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
								cell.setCellValue((Boolean)value);
							} 
							else {
								cell.setCellType(HSSFCell.CELL_TYPE_STRING);
								cell.setCellValue(value.toString());  
							}
						} else {
							cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
						}
					} else {

						cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
						if (!blankCellBold) {
							cell.setCellStyle(noneStyle);
						} else {
							cell.setCellStyle(borderStyle);
						}
					}
				}
			}
		}
	}


	/**
	 * 将结果集填充到Excel模版,resultset必须是以Map封装行
	 * @param data 数据内容
	 * @throws Exception
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	public void generate(List<?> data) throws Exception {
		this.generate(data, null);

	}

	/**
	 * 将结果集填充到Excel模版,resultset必须是以Map封装行
	 * @param resultset 结果集
	 * @param exportInfo 包含图片路径的对象
	 * @throws Exception
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	public void generate(List<?> resultset, Object exportInfo) throws Exception {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
				templateFile));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		initialize(sheet);
		if (startRow == -1)
			return;

		// 先填充标题
		if (exportInfo != null)
			this.generateTitleDatas(exportInfo, wb, sheet, patriarch);
		// 生成数据内容
		this.generateContentDatas(resultset, wb, sheet, patriarch);
		if (hasFormula) {
			this.calcFormula(wb, sheet);
		}

		FileOutputStream fileOut = new FileOutputStream(outputFile);
		wb.write(fileOut);
		fileOut.close();
	}

	
	/**
	 * 获取默认单元格边框样式
	 * @param wb excel工作簿
	 * @return 单元格边框样式
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	//TODO 边框样式？
	private HSSFCellStyle getBorderStyle(HSSFWorkbook wb) {
		if ( borderStyle == null ) {
			HSSFCellStyle style = wb.createCellStyle();
			HSSFFont font = wb.createFont();
			font.setFontHeightInPoints((short) fontSize);
			font.setFontName(fontName);
			style.setFont(font);
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);
			borderStyle = style;
		}
		return borderStyle;
	}

	/**
	 * 获取默认单元格无边框样式
	 * @param wb excel工作簿
	 * @return 无边框样式
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	private HSSFCellStyle getNoneStyle(HSSFWorkbook wb) {
		if ( noneStyle == null ) {
			HSSFCellStyle style = wb.createCellStyle();
			HSSFFont font = wb.createFont();
			font.setFontHeightInPoints((short) fontSize);
			font.setFontName(fontName);
			style.setFont(font);
			style.setBorderBottom(HSSFCellStyle.BORDER_NONE);
			style.setBorderLeft(HSSFCellStyle.BORDER_NONE);
			style.setBorderRight(HSSFCellStyle.BORDER_NONE);
			style.setBorderTop(HSSFCellStyle.BORDER_NONE);
			noneStyle = style;
		}
		return noneStyle;
	}

	/**
	 * 向下平推表格，并复制格式与内容
	 * @param sheet 工作表
	 * @param thisrow 当前行号
	 * @param lastrow 最后行号
	 * @param shiftcount 平推量
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	private void shiftDown(HSSFSheet sheet, int thisrow, int lastrow,
			int shiftcount) {
		sheet.shiftRows(thisrow, lastrow, shiftcount);

		for (int z = 0; z < shiftcount; z++) {
			HSSFRow row = sheet.getRow(thisrow);
			HSSFRow oldrow = sheet.getRow(thisrow + shiftcount);
			// 将各行的行高复制
			oldrow.setHeight(row.getHeight());
			// 将各个单元格的格式复制
			for (int i = 0; i <= oldrow.getPhysicalNumberOfCells(); i++) {

				HSSFCell cell = row.createCell(i);
				HSSFCell oldcell = oldrow.getCell(i);

				if (oldcell != null) {
					switch (oldcell.getCellType()) {
					case HSSFCell.CELL_TYPE_STRING:
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(oldcell.getRichStringCellValue());
						break;
					case HSSFCell.CELL_TYPE_NUMERIC:
						cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
						cell.setCellValue(oldcell.getNumericCellValue());
						break;
					default:
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(oldcell.getRichStringCellValue());

					}
					cell.setCellStyle(oldcell.getCellStyle());
				}
			}

			// 将有列跨越的复制
			Vector<Region> regs = findRegion(sheet, oldrow);
			if (regs.size() != 0) {
				for (int i = 0; i < regs.size(); i++) {
					Region reg = regs.get(i);
					reg.setRowFrom(row.getRowNum());
					reg.setRowTo(row.getRowNum());
					sheet.addMergedRegion(reg);
				}
			}
			thisrow++;
		}
	}

	/**
	 * 查找所有的合并单元格
	 * @param sheet 工作表
	 * @param oldrow 当前行
	 * @return 合并合并单元格Vector
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	//TODO oldrow
	@SuppressWarnings("deprecation")
	private Vector<Region> findRegion(HSSFSheet sheet, HSSFRow oldrow) {
		Vector<Region> regs = new Vector<Region>();
		int num = sheet.getNumMergedRegions();//取合并格个数
		int curRowid = oldrow.getRowNum();//获取当前行号
		for (int i = 0; i < num; i++) {
			Region reg = sheet.getMergedRegionAt(i);//取第X个合并格的合并区域
			if (reg.getRowFrom() == reg.getRowTo()
					&& reg.getRowFrom() == curRowid) {
				regs.add(reg);
			}
		}
		return regs;
	}

	/**
	 * 将列的索引换算成ABCD字母，这个方法要在插入公式时用到
	 * @param c 列索引。
	 * @return ABCD字母。
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	@SuppressWarnings("unused")
	private int getColumnIndex(char c) {
		int i = c;
		return i - 65;
	}

	/**
	 * 判断是否无效Cell
	 * @param cell 单元格
	 * @return 单元格是否有效
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	@SuppressWarnings("unused")
	private boolean invalidCellValue(HSSFCell cell) {
		if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
			return true;
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
			if (cell.getRichStringCellValue().getString() == null
					|| cell.getRichStringCellValue().getString().equals("")) {
				return true;
			}
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR) {
			return true;
		}

		return false;
	}

	/**
	 * 如果模板文件是否存在
	 * @param templateFile 指定模板文件名
	 * @return 文件存在返回true,否则false
	 * @throws IOException
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	protected boolean isExistTemplate(String templateFile) throws IOException {
		File file = new File(templateFile);
		return file.exists();

	}

	/**
	 * 从目标bean通过关键字获取指定属性
	 * @param data 数据bean
	 * @param property 属性关键字
	 * @return 指定属性
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	//TODO
	private Object getValue(Object data, String property) {
		// return XPathLocator.newInstance().getValue(data, property);
		try {
//			org.apache.commons.beanutils.PropertyUtils.getp
			return PropertyUtils.getProperty(data, property);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 单元格内插入图片
	 * @param wb excel工作簿
	 * @param sheet 工作表
	 * @param imageFile 图片目录
	 * @param cell 单元格
	 * @param patriarch 画图的顶级管理器
	 * @throws Exception
	 * @author:Fisher
	 * @email:zhuangcaijin@126.com
	 */
	//TODO
	private void drawImage(HSSFWorkbook wb, HSSFSheet sheet, String imageFile,
			HSSFCell cell, HSSFPatriarch patriarch) throws Exception {

		// 插入图片
		String ext = imageFile.substring(imageFile.lastIndexOf('.') + 1);
		int format = HSSFWorkbook.PICTURE_TYPE_PNG;
		if ("jpg".equalsIgnoreCase(ext) || "jpeg".equalsIgnoreCase(ext)
				|| "jpe".equalsIgnoreCase(ext) || "jfif".equalsIgnoreCase(ext)) {
			format = HSSFWorkbook.PICTURE_TYPE_JPEG;
		}
		if ("emf".equalsIgnoreCase(ext)) {
			format = HSSFWorkbook.PICTURE_TYPE_EMF;
		}
		ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
		BufferedImage bufferImg = null;
		if ( imageFile.startsWith("http") ) {
			URL url = new URL(imageFile);
			bufferImg = ImageIO.read(url);
		} else {
			bufferImg = ImageIO.read(new File(imageFile));
		}
		ImageIO.write(bufferImg, ext, byteArrayOut);
		// 设置图片大小
		int x = bufferImg.getWidth();
		int y = bufferImg.getHeight();
		double scale = 1.0d;
		if ( y > maxRowHeight ) {
			scale = 1.0d * maxRowHeight / y;
			y = maxRowHeight;
			x = (int) (x * scale);
		}
		HSSFClientAnchor anchor = new HSSFClientAnchor();
		anchor.setAnchor((short) cell.getColumnIndex(), cell.getRowIndex(), 0,
				0, (short) cell.getColumnIndex(), cell.getRowIndex(), x, y);
		anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);
		HSSFPicture pic = patriarch.createPicture(anchor, wb.addPicture(
				byteArrayOut.toByteArray(), format));
		
		// 行高与宽长，都为像素的20倍
		cell.getRow().setHeight((short) (y * 20 + 20) );
		if (sheet.getColumnWidth(cell.getColumnIndex()) < x * 20 + 20) {
			sheet.setColumnWidth(cell.getColumnIndex(), x * 20 + 20);
		}
		pic.resize(scale);
		anchor.setDx1(10);
		anchor.setDy1(10);
		byteArrayOut.close();
	}

}
